package cn.wh.xwm.common.util;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;

import jxl.Cell;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;

import cn.wh.xwm.common.exception.ExcelException;
import cn.wh.xwm.controller.action.vo.LiquidItem;

/**
 * excel处理工具类
 * 
 * @author swimming
 * 
 */
public class ExcelParshUtils {

	private static ExcelParshUtils utils = null;

	public static ExcelParshUtils instance() {
		if (ExcelParshUtils.utils == null) {
			utils = new ExcelParshUtils();
		}
		return utils;
	}

	private ExcelParshUtils() {
	}

	/**
	 * EXCEL文件格式是否符合
	 * 
	 * @param file
	 * @return
	 * @throws IOException
	 * @throws BiffException
	 */
	public boolean validateFile(File file) {
		boolean tag = true;
		try {
			WorkbookSettings workbookSettings = new WorkbookSettings();
			workbookSettings.setEncoding("UTF-8");
			Workbook book = Workbook.getWorkbook(file, workbookSettings);
			int sheetSize = Constant.SHEETSIZE;
			for (int i = 0; i < sheetSize; i++) {
				Sheet sheet = book.getSheet(i);
				int rows = sheet.getRows();
				for (int j = 1; j < rows; j++) {
					String date = sheet.getCell(0, j).getContents().trim();
					if (!TimeUtils.isDateFormat(date)) {
						return false;
					}
					String amount = sheet.getCell(2, j).getContents().trim();
					try {
						Integer.valueOf(amount);
					} catch (Exception e) {
						return false;
					}
					String inOrOut = sheet.getCell(4, j).getContents().trim();
					if (!(inOrOut.equals(Constant.INVENTORY_IN) || inOrOut
							.equals(Constant.INVENTORY_OUT))) {
						return false;
					}
				}
			}
			book.close();
		} catch (Exception e) {
			tag= false;
		}
		return tag;
	}

	/**
	 * 
	 * @param file
	 * @return map key=产品名 value= 数据集合
	 * @throws IOException
	 * @throws BiffException
	 * @throws ExcelException
	 */
	public HashMap<String, ArrayList<LiquidItem>> getLiquiidsFromFile(File file)
			throws BiffException, IOException {

		HashMap<String, ArrayList<LiquidItem>> map = new HashMap<String, ArrayList<LiquidItem>>();
		WorkbookSettings workbookSettings = new WorkbookSettings();
		workbookSettings.setEncoding("UTF-8");
		Workbook book = Workbook.getWorkbook(file, workbookSettings);
		int sheetSize = Constant.SHEETSIZE;
		for (int i = 0; i < sheetSize; i++) {
			Sheet sheet = book.getSheet(i);
			ArrayList<LiquidItem> templist = new ArrayList<LiquidItem>();
			map.put(BusinessUtil.getKey(i), templist);
			int rows = sheet.getRows();
			for (int j = 1; j < rows; j++) {
				LiquidItem item = new LiquidItem();
				DateCell dc = (DateCell) sheet.getCell(0, j);
				item.setEventDate(dc.getDate());
				item.setCustomerName(sheet.getCell(1, j).getContents().trim());
				item.setAmount(sheet.getCell(2, j).getContents().trim());
				item.setPatchNumber(sheet.getCell(3, j).getContents().trim());
				item.setInOrOut(sheet.getCell(4, j).getContents().trim());
				templist.add(item);
			}
		}
		book.close();
		return map;
	}
	 
	/**
	 * 读取excel第n个sheet中，第n列的记录
	 * @param file
	 * @return
	 * @throws IOException 
	 * @throws BiffException 
	 */
	public ArrayList<String> readFirstColumn(File file,int column,int sheetnumber) throws BiffException, IOException{
		ArrayList<String> list = new ArrayList<String>();
		WorkbookSettings workbookSettings = new WorkbookSettings();
		workbookSettings.setEncoding("UTF-8");
		Workbook book = Workbook.getWorkbook(file, workbookSettings);
		Sheet sheet = book.getSheet(sheetnumber);
		int rows = sheet.getRows();
		for(int i=0;i<rows;i++){
			Cell cc = sheet.getCell(column, i);
			list.add(cc.getContents().trim());
		}
		return list;
	}
	
	/**
	 * 读取excel文件前两列到数组中，并放入集合
	 * @param file
	 * @param column
	 * @return
	 * @throws IOException 
	 * @throws BiffException 
	 */
	public ArrayList<String[]> readRelatedTwoColumn(File file,int columnA,int columnB,int sheetnumber) throws BiffException, IOException{
		ArrayList<String[]> list = new ArrayList<String[]>();
		WorkbookSettings workbookSettings = new WorkbookSettings();
		workbookSettings.setEncoding("UTF-8");
		Workbook book = Workbook.getWorkbook(file, workbookSettings);
		Sheet sheet = book.getSheet(sheetnumber);
		int rows = sheet.getRows();
		for(int i=0;i<rows;i++){
			Cell ccA = sheet.getCell(columnA, i);
			Cell ccB = sheet.getCell(columnB, i);
			String[] temp = new String[2];
			temp[0]=ccA.getContents().trim();
			temp[1]=ccB.getContents().trim();
			list.add(temp);
		}
		return list;
	}
}
